package belf.migrate.plugin.shentong;

import belf.migrate.api.exception.DataTypeMappingNotExistException;
import belf.migrate.api.table.schema.*;
import belf.migrate.api.taskconf.TaskConf;
import belf.migrate.api.taskconf.typemapping.TypeMappingChecker;
import belf.migrate.api.util.CatalogUtils;
import belf.migrate.api.util.DatabaseType;
import belf.migrate.core.jdbc.DBFunctionMapping;
import belf.migrate.core.jdbc.DBTypeMapping;
import belf.migrate.core.jdbc.FunctionModel;
import belf.migrate.core.jdbc.TypeModel;
import belf.migrate.core.util.StringTool;
import org.apache.commons.lang3.StringUtils;

import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;
import java.util.function.Predicate;
import java.util.stream.Collectors;

import static belf.migrate.api.common.Preconditions.checkNotNull;

/**
 * 通过原表的字段元信息，构建目标表
 */
public class ShentongCreateTableSqlBuilder {

    private final TablePath tablePath;
    private TaskConf taskConf = null;

    private final List<ColumnMetaData> columns;
    private final PrimaryKey primaryKey;
    private final List<ForeignKey> foreignKeys;
    private final List<IndexKey> indexKeys;
    private final List<UniqueKey> uniqueKeys;
    private final List<CheckConstraint> checkConstraints;


    private String fieldCase;

    private String taskType;
    /**
     * 字段映射关系
     */
    private final Map<String, TypeModel> mappings;
    /**
     * 函数映射关系
     */
    private final Map<String, FunctionModel> funcMappings;

    private ShentongCreateTableSqlBuilder(TablePath tablePath, TableSchema sourceTableSchema, String taskType) {
        this(tablePath, sourceTableSchema, taskType, null);
    }
    /**
     * 通过目标表卢健、原表元数据、任务类型创建构造方法
     * @param tablePath 目标表路径
     * @param sourceTableSchema 原表的元数据信息，含字段信息、主键、外键、索引
     * @param taskType 表示进行迁移任务的类型，比如“MYSQL-DAMENG”。为各种mapping.xml的root element的ID
     */
    private ShentongCreateTableSqlBuilder(TablePath tablePath, TableSchema sourceTableSchema, String taskType, TaskConf taskConf) {
        checkNotNull(tablePath.getTableName(), "tableName must not be null");
        this.tablePath = tablePath;
        this.columns = sourceTableSchema.getColumns();
        this.primaryKey = sourceTableSchema.getPrimaryKey();
        this.foreignKeys = sourceTableSchema.getForeignKeys();
        this.indexKeys = sourceTableSchema.getIndexKeys();
        this.uniqueKeys = sourceTableSchema.getUniqueKeys();
        this.checkConstraints = sourceTableSchema.getCheckConstraints();
        this.mappings = DBTypeMapping.getInstance().getMapping(taskType);
        this.funcMappings = DBFunctionMapping.getInstance().getMapping(taskType);
        this.taskType = taskType;
        this.taskConf = taskConf;
    }

    public static ShentongCreateTableSqlBuilder builder(TablePath tablePath,
                                                      TableSchema sourceTableSchema,
                                                      String taskType) {
        return new ShentongCreateTableSqlBuilder(tablePath, sourceTableSchema, taskType);
    }
    public static ShentongCreateTableSqlBuilder builder(TablePath tablePath,
                                                      TableSchema sourceTableSchema,
                                                      String taskType,
                                                      TaskConf taskConf) {
        return new ShentongCreateTableSqlBuilder(tablePath, sourceTableSchema, taskType, taskConf);
    }

    public ShentongCreateTableSqlBuilder fieldCase(String fieldCase) {
        this.fieldCase = fieldCase;
        return this;
    }

    public List<String> createIndexes() {
        List<String> indexeSqls = new ArrayList<>();
        String sqlTemplate = "CREATE %s INDEX %s ON %s (%s %s)";
        indexKeys.forEach(new Consumer<IndexKey>() {
            @Override
            public void accept(IndexKey indexKey) {
                //不用对主键再建立索引: mysql 对主键返回索引名是“PRIMARY”
                if ("PRIMARY".equalsIgnoreCase(indexKey.getIndexName())){
                    return;
                }

                // 对于GBase，在创建表时，如果源端定义了字段（或联合字段）为UNIQUE，则自动创建索引
                // 所以在后续创建索引的任务时，要排除UNIQUE，否则会失败
                if (indexKey.isUnique()) {
                    return;
                }

                //对于BLOB、CLOB字段不能建立索引
                for (String columnName: indexKey.getColumnNames()) {
                    if (isCLOBOrBLOB(columnName)) {
                        return;
                    }
                }

                //不用对主键再建立索引: 主键字段完全包含索引字段
                if (String.join(",", primaryKey.getColumnNames()).toUpperCase()
                        .contains(String.join(",", indexKey.getColumnNames()).toUpperCase())){
                    return;
                }

                String PREFIX = "";
                if (indexKey.isUnique()){
                    PREFIX = "UNIQUE ";
                }
                String indexName = indexKey.getIndexName();
                String tableName = tablePath.getFullNameWithQuoted();
                String columnNames = String.join(",", indexKey.getColumnNames()).toUpperCase();
                String asc = "";
                if (indexKey.getIndexOrder() == IndexKey.IndexOrder.ASC) {
                    asc = "ASC";
                } else if (indexKey.getIndexOrder() == IndexKey.IndexOrder.DESC) {
                    asc = "DESC";
                }

                String newIndexName = "IDX_" + tablePath.getTableName() + "_"
                        + String.join("_", indexKey.getColumnNames());
                String indexSql = String.format(sqlTemplate, PREFIX, newIndexName, tableName, columnNames, asc);
                indexeSqls.add(indexSql);
            }
        });
        return indexeSqls;
    }

    private boolean isCLOBOrBLOB(String columnName) {
        return columns.stream().anyMatch(new Predicate<ColumnMetaData>() {
            @Override
            public boolean test(ColumnMetaData columnMetaData) {
                if (columnMetaData.getColumnName().equalsIgnoreCase(columnName)
                        && CatalogUtils.isCLOBOrBLOB(columnMetaData.getColumnType())
                ) {
                    return true;
                }
                return false;
            }
        });
    }

    public List<String> createForeigns() {
        List<String> foreignSqls = new ArrayList<>();
        foreignKeys.forEach(new Consumer<ForeignKey>() {
            @Override
            public void accept(ForeignKey foreignKey) {
                StringBuilder sb = new StringBuilder();
                sb.append("ALTER TABLE ");
                sb.append(tablePath.getFullNameWithQuoted().toUpperCase());
                sb.append("ADD CONSTRAINT \"");
                //直接用原有的外键名，可能与索引名冲突
//                sb.append(foreignKey.getFkName().toUpperCase());
                sb.append("FK_" + tablePath.getTableName() + "_" + foreignKey.getPkTable().toUpperCase() + "_" + foreignKey.getFkColumnName());
                sb.append("\" FOREIGN KEY (\"");
                sb.append(foreignKey.getFkColumnName().toUpperCase());
                sb.append("\") REFERENCES \"");
                if (foreignKey.getPkCatalog() != null) {
                    sb.append(foreignKey.getPkCatalog().toUpperCase());
                    sb.append("\".\"");
                } else if (foreignKey.getPkSchema() != null) {
                    sb.append(foreignKey.getPkSchema().toUpperCase());
                    sb.append("\".\"");
                }
                sb.append(foreignKey.getPkTable().toUpperCase());
                sb.append("\"(\"");
                sb.append(foreignKey.getPkColumnName().toUpperCase());
                sb.append("\")");
                if (foreignKey.getDeleteRule() != null
                        && (foreignKey.getDeleteRule().equals(ForeignKey.ConstraintAction.CASCADE)
                        || foreignKey.getDeleteRule().equals(ForeignKey.ConstraintAction.RESTRICT))) {
                    sb.append(" ON DELETE " + foreignKey.getDeleteRule().getExpression());
                }
                if (foreignKey.getUpdateRule() != null
                        && (foreignKey.getUpdateRule().equals(ForeignKey.ConstraintAction.CASCADE)
                        || foreignKey.getUpdateRule().equals(ForeignKey.ConstraintAction.RESTRICT))) {
                    sb.append(" ON UPDATE " + foreignKey.getDeleteRule().getExpression());
                }
                sb.append(";");

                foreignSqls.add(sb.toString());
            }
        });
        return foreignSqls;
    }

    public String createTable() throws DataTypeMappingNotExistException {
        List<String> sqls = new ArrayList<>();
        sqls.add(
                String.format(
                        "CREATE TABLE %s (\n%s",  // FIXME: 可能需要加上schema，后面验证
                        tablePath.getSchemaAndTableName(),
                        buildColumnsIdentifySql()).trim());
        if (primaryKey != null && !primaryKey.getColumnNames().isEmpty()) {
            sqls.add(",\n\t" + buildPrimaryKeySql());
        }

        if (uniqueKeys != null && !uniqueKeys.isEmpty()) {
            sqls.add(",\n\t" + buildUniqueKeySql());
        }

        if (checkConstraints != null && !checkConstraints.isEmpty()) {
            sqls.add(",\n\t" + buildCheckConstraintSql());
        }
        return String.join(" ", sqls) + "\n);";
    }

    private String buildColumnsIdentifySql() throws DataTypeMappingNotExistException {
        List<String> columnSqls = new ArrayList<>();
        for (ColumnMetaData column : columns) {
            columnSqls.add("\t" + buildColumnIdentifySql(column));
        }

        return String.join(", \n", columnSqls);
    }

    private String buildColumnIdentifySql(ColumnMetaData column) throws DataTypeMappingNotExistException {
        final List<String> columnSqls = new ArrayList<>();
        columnSqls.add(CatalogUtils.quoteIdentifier(column.getColumnName(), fieldCase, DatabaseType.SHENTONG.getQuoteType().getValue(), DatabaseType.SHENTONG.getQualifiedType()));

        String[] typenames = column.getColumnTypeName().split("\\s+");
        String typename = typenames[0].toUpperCase();
        String targetColumnType = "";

        // 先检查表级字段类型映射，确定目标端的字段类型
        targetColumnType = TypeMappingChecker.tableMapping(taskConf, column);
        // 再检查库级的数值类型映射，确定目标端的字段类型
        if (StringUtils.isEmpty(targetColumnType)) {
            targetColumnType = TypeMappingChecker.databaseMapping(taskConf, column);
        }

        // FIXME: 如果预定义模板漏掉了一种源端数据类型，这里可能出错，但是没有报异常，需要后面修复
        if (StringUtils.isEmpty(targetColumnType) && mappings.containsKey(typename)) {
            // 没有定义表级字段类型映射和库级数值类型映射，直接用模板中预定义的类型映射来确定目标端的字段类型
            if (mappings.containsKey(typename)) {
                if (StringUtils.isEmpty(targetColumnType)) {
                    targetColumnType = mappings.get(typename).getTo();
                }
                columnSqls.add(checkColumnLength(column, targetColumnType));  //字段数据类型
            } else {
                throw new DataTypeMappingNotExistException(taskConf.getTaskType(), typename);
            }
        }

        // 字段值是否允许为空
        if (column.getNullable() == 0) {
            columnSqls.add("NOT NULL");
        }

        //TODO： 神通不支持UNSIGNED关键字，后续再完善
//        for (int i = 1; i < typenames.length; i++) {
//            columnSqls.add(typenames[i]);
//        }

        // 字段默认值
        if (column.getExtra() != null && column.getExtra().getDefaultValue() != null) {
            String defaultAttribute = null;
            ColumnExtra extra = column.getExtra();
            String defaultValue = StringTool.trimParentheses(extra.getDefaultValue());
            if (CatalogUtils.isTime(column.getColumnType())) { //先处理时间字段的默认值
                if (extra.getDefaultValue() != null) {
                    String sourceFuncName = StringTool.getFunctionName(defaultValue);
                    // 对Source端函数进行替换，如果找到可替换函数，再添加DEFAULT值
                    if (funcMappings != null
                            && StringUtils.isNotEmpty(sourceFuncName)
                            && funcMappings.containsKey(sourceFuncName.toUpperCase())) {
                        String sinkFuncName = funcMappings.get(sourceFuncName.toUpperCase()).getTo();
                        if (StringUtils.isNotEmpty(sinkFuncName)) {
                            if (!(defaultValue.endsWith(")"))) { //MySQL的"CURRENT_TIMESTAMP"函数没有以()结尾，需要加上
                                defaultValue += "()";
                            }
                            defaultAttribute = " DEFAULT " + sinkFuncName + defaultValue.substring(defaultValue.indexOf('('));
                        }
                    }
                }
                // 处理日期类型字段在行进行UPDATE和DELETE时的联动动作
                // 注意：神通在定义字段时不能像mysql一样支持ON UPDATE 或者 ON DELETE动作
//                if (defaultAttribute != null && extra.getExtra() != null && extra.getExtra().toUpperCase().startsWith("ON")) {
//                    defaultAttribute += " " + extra.getExtra();
//                }
            } else if ("java.lang.Boolean".equalsIgnoreCase(column.getColumnClassName())) { //再处理其他基本字段的默认值
                // BIT or BOOLEAN in java.sql.Type can be regarded as Boolean type
                if ("TRUE".equalsIgnoreCase(defaultValue)
                        || "T".equalsIgnoreCase(defaultValue)
                        || "1".equalsIgnoreCase(defaultValue)) {
                    defaultAttribute = " DEFAULT TRUE";
                } else {
                    defaultAttribute = " DEFAULT FALSE";
                }
            } else if (CatalogUtils.isNumeric(column.getColumnType()) || column.getColumnType() == Types.BOOLEAN) { //再处理其他基本字段的默认值
                if (extra.getDefaultValue() != null) {
                    defaultAttribute = " DEFAULT " + extra.getDefaultValue();
                }
            } else if (CatalogUtils.isString(column.getColumnType())) { //再处理其他基本字段的默认值
                if (extra.getDefaultValue() != null) {
                    defaultAttribute = " DEFAULT '" + extra.getDefaultValue() + "'";
                }
            } else {
                if (extra.getDefaultValue() != null) {
                    defaultAttribute = " DEFAULT " + extra.getDefaultValue();
                }
            }

            if (defaultAttribute != null) {
                columnSqls.add(defaultAttribute);
            }
        }
        // 是否自增字段
        if (column.isAutoIncrement()) {
            columnSqls.add("AUTO_INCREMENT");
        }

        return String.join(" ", columnSqls);
    }

    /**
     * 达梦各种数值类型精度和标度的定义，参考：
     * <a href="https://eco.dameng.com/document/dm/zh-cn/pm/dm8_sql-data-types-operators">DMSQL 程序数据类型与操作符</a>
     * @param column
     * @param targetColumnType
     * @return
     */
    private String checkColumnLength(ColumnMetaData column, String targetColumnType) {
        String retValue = targetColumnType;
        int columnType = column.getColumnType();

        // 时间类型单独处理精度，各种数据库一般有单独的表存放Column定义，这个表里有单独字段存放时间字段的精度
        if (columnType == Types.TIMESTAMP ||
                columnType == Types.TIMESTAMP_WITH_TIMEZONE ||
                columnType == Types.TIME) {
            if (column.getExtra() != null && column.getExtra().getDateTimePrecision() >= 0) {
                retValue += "(" + column.getExtra().getDateTimePrecision() + ")";
            }
        }

        // 已经指定了有明确长度的数值类型，后面不能再加(length)
        switch(targetColumnType.toUpperCase()) {
            case "NUMERIC": //以下类型可能加精度和标度
            case "NUMBER":
            case "DECIMAL":
            case "DEC":
            case "BYTE":
                if (column.getScale() > 0) {
                    retValue += "(" + column.getPrecision() + "," + column.getScale() + ")";
                } else if (column.getPrecision() > 0) {
                    retValue += "(" + column.getPrecision()  + ")";
                }
                return retValue;
            case "FLOAT":  //以下类型可能加精度
            case "DOUBLE":
            case "CHAR":
            case "CHARACTER":
            case "VARCHAR":
            case "NCHAR":
            case "NVARCHAR":
                if (column.getPrecision() > 0) {
                    retValue += "(" + column.getPrecision()  + ")";
                }
                return retValue;
            default: //其他类型没有精度和标度
                return targetColumnType;
        }
    }

    private String buildPrimaryKeySql() {
        String key =
                primaryKey.getColumnNames().stream()
                        .map(columnName -> "\"" + columnName + "\"")
                        .collect(Collectors.joining(", "));
        // add sort type
        return String.format("PRIMARY KEY (%s)", CatalogUtils.quoteIdentifier(key, fieldCase));
    }

    private String buildUniqueKeySql() {
        List<String> sqls = new ArrayList<>();
        for (UniqueKey uniqueKey : uniqueKeys) {
            String sql = String.format("UNIQUE(%s)", String.join(",", uniqueKey.getColumns()));
            sqls.add(sql);
        }
        return String.join(",\n\t", sqls);
    }

    private String buildCheckConstraintSql() {
        List<String> sqls = new ArrayList<>();
        for (CheckConstraint checkConstraint : checkConstraints) {
            String condition = checkConstraint.getCondition();
            condition = condition.replace('`', '\"');
            condition = StringTool.removeChars(condition, "[]");
            String sql = String.format("CONSTRAINT \"%s\" CHECK (%s)",
                    checkConstraint.getConstraintName(),
                    condition);
            sqls.add(sql);
        }
        return String.join(",\n\t", sqls);
    }
}
